﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for TinhnangController
/// </summary>
public class TinhnangController:Interface
{
    private SqlConnection con;
	public TinhnangController()
	{
	    con = ConnectDB.Connect();
        ConnectDB.Open();
	}

    public int Insert(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert_Tinhnang";
            cmd.CommandType = CommandType.StoredProcedure;
            Tinhnang tn = (Tinhnang)obj;
            cmd.Parameters.Add("@group_tinhnang_id", SqlDbType.Int).Value = tn.GroupTinhnang_id;
            cmd.Parameters.Add("@name", SqlDbType.NText).Value = tn.Name;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = tn.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = tn.Status;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Update(object obj)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update_Tinhnang";
            cmd.CommandType = CommandType.StoredProcedure;
            Tinhnang tn = (Tinhnang)obj;
            cmd.Parameters.Add("@tinhnang_id", SqlDbType.Int).Value = tn.Tinhnang_id;
            cmd.Parameters.Add("@group_tinhnang_id", SqlDbType.Int).Value = tn.GroupTinhnang_id;
            cmd.Parameters.Add("@name", SqlDbType.NText).Value = tn.Name;
            cmd.Parameters.Add("@order", SqlDbType.Int).Value = tn.Order;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = tn.Status;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public int Delete(int id)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete_Tinhnang";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tinhnang_id", SqlDbType.Int).Value = id;
            return cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            return 0;
            throw;
        }
    }

    public DataTable GetAll()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Tinhnang WHERE [status]=1 ORDER BY [order] ASC";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public List<Tinhnang> getAllTinhnang()
    {
        List<Tinhnang> list = new List<Tinhnang>();
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT tn.* FROM Group_Tinhnang AS gt INNER JOIN Tinhnang AS tn ON gt.group_tinhnang_id = tn.group_tinhnang_id WHERE gt.[status]=1 AND tn.[status]=1 ORDER BY gt.[order] ASC,tn.[order] ASC, gt.group_tinhnang_id ASC ";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            foreach (DataRow item in dt.Rows)
            {
                Tinhnang tn = new Tinhnang(item);
                list.Add(tn);
            }
            return list;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
    public DataTable GetByID(int id)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM Select_All_Tinhnang WHERE tinhnang_id=@tinhnang_id AND [status]=1 ORDER BY [order] ASC";
            cmd.Parameters.AddWithValue("@tinhnang_id", id);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            cmd.Connection = con;
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            return null;
            throw;
        }
    }
}